package BS.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;



import BS.entity.caozuoyuan;
import BS.utils.pageinfo;
import BS.utils.propertiesUtils;

public class caozuoyuanDao {
	
	public void add(caozuoyuan caozuoyuan) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String sql = "insert into caozuoyuan(cid,name,num,guishu,psd) values(?,?,?,?,?)";
	    queryRunner.update(sql,caozuoyuan.getCid(),caozuoyuan.getName(),caozuoyuan.getNum(),caozuoyuan.getGuishu(),caozuoyuan.getPsd());
	}
	public void delete(Integer id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String sql = "delete from caozuoyuan where id = ?";
		queryRunner.update(sql,id);
	}
	public void update(caozuoyuan caozuoyuan) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String sql = "update caozuoyuan set cid = ?,name = ?,num = ?,guishu = ? where id = ?";
		queryRunner.update(sql,caozuoyuan.getCid(),caozuoyuan.getName(),caozuoyuan.getNum(),caozuoyuan.getGuishu(),caozuoyuan.getId());
	}
	public pageinfo<caozuoyuan> list(caozuoyuan caozuoyuan,pageinfo<caozuoyuan> pageinfo) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String _sql = "";
		List<Object> _list = new ArrayList<Object>();
		if(StringUtils.isNoneBlank(caozuoyuan.getCid())) {
			_sql += " and CID like ?";
			_list.add("%"+caozuoyuan.getCid()+"%");
		}
		if(StringUtils.isNoneBlank(caozuoyuan.getName())) {
			_sql += " and NAME like ?";
			_list.add("%"+caozuoyuan.getName()+"%");
		}
		//_listת����
		Object[] arr = new Object[_list.size()];
		for (int i=0;i<_list.size();i++) {
			arr[i] = _list.get(i);
		}
		String sql = "select * from caozuoyuan where 1=1 "+_sql+" limit  "+(pageinfo.getPageno()-1)*pageinfo.getPagesize()+" , "+pageinfo.getPagesize();
		List<caozuoyuan> list = queryRunner.query(sql, new BeanListHandler<>(caozuoyuan.class),arr);
		
		pageinfo.setList(list);
		pageinfo.setTotalcount(this.count(caozuoyuan)); 
		return pageinfo;
	}
	
	public caozuoyuan findById(Integer id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String sql = "select * from caozuoyuan where id = ?";
		caozuoyuan caozuoyuan = queryRunner.query(sql,new BeanHandler<>(caozuoyuan.class),id);
		return caozuoyuan;
	}
	public caozuoyuan login(String cid,String psd) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String sql = "select * from caozuoyuan where cid = ? and psd = ?";
		caozuoyuan entity = queryRunner.query(sql,new BeanHandler<>(caozuoyuan.class),cid,psd);
		return entity;
	}
	
	
	
	public Long count(caozuoyuan caozuoyuan) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String _sql = "";
		List<Object> _list = new ArrayList<Object>();
		if(StringUtils.isNoneBlank(caozuoyuan.getCid())) {
			_sql += " and CID like ?";
			_list.add("%"+caozuoyuan.getCid()+"%");
		}
		if(StringUtils.isNoneBlank(caozuoyuan.getName())) {
			_sql += " and NAME like ?";
			_list.add("%"+caozuoyuan.getName()+"%");
		}
		//_listת����
		Object[] arr = new Object[_list.size()];
		for (int i=0;i<_list.size();i++) {
			arr[i] = _list.get(i);
		}
		String sql = "select count(*) from caozuoyuan where 1=1 "+_sql;
		Long count = (Long)queryRunner.query(sql,new ScalarHandler(),arr);
		return count;
	}
	public void update(String pwd, Integer id)throws SQLException {
		QueryRunner queryRunner = new QueryRunner(propertiesUtils.getDataSource());
		String sql = "update caozuoyuan set psd = ? where id = ? ";
		queryRunner.update(sql, pwd,id);
	}
	
}


